package com.fingard.db;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.fingard.FGBiz;
import com.fingard.constant.Format;

public class JDBCHelper {

    /**
     * 释放statement
     * @param statement
     */
    private static void close(Statement statement) {
        try {
        	if(statement!=null){
        		statement.close();
        	}
        } catch (SQLException e) {
            FGBiz.limitMsg.exception("JDBCHelper.close", e);
        }
    }

    /**
     * 释放resultset
     * @param rs
     */
    private static void close(ResultSet rs) {
        try {
        	if(rs!=null){
        		rs.close();
        	}
        } catch (SQLException e) {
        	FGBiz.limitMsg.exception("JDBCHelper.close", e);
        }
    }

    /**
     * 释放资源
     *
     * @param statement
     * @param rs
     */
    public static void close( Statement statement, ResultSet rs) {
        if (rs != null) {
            close(rs);
        }
        if (statement != null) {
            close(statement);
        }
    }

    public static Map<String, Object> execQuerySingle(Connection p_conn, String p_sql, Object[] p_paramters) throws SQLException {
        ResultSet rs = null;
        PreparedStatement pre = null;
        Map<String, Object> map = new HashMap<String, Object>();
        int index = 1;
        try {
            pre = p_conn.prepareStatement(p_sql);
            if(p_paramters!=null){
	            for (int i = 0; i < p_paramters.length; i++) {
	                if (p_paramters[i] == null) {
	                    pre.setObject(index++, "");
	                } else {
	                    pre.setObject(index++, p_paramters[i]);
	                }
	            }
            }
            //System.out.println(pre.toString());
            rs = pre.executeQuery();

            if(rs.next()){
                ResultSetMetaData metaData = rs.getMetaData();
                int cols_len = metaData.getColumnCount();
                for (int i = 0; i < cols_len; i++) {
                    String cols_name = metaData.getColumnLabel(i + 1);
                    Object cols_value = rs.getObject(cols_name);
                    if (cols_value == null) {
                        cols_value = "";
                    }
                    map.put(cols_name, cols_value);
                }
            }

            return map;
        } catch (SQLException e) {
            throw new SQLException(e);
        } finally {
            close(pre, rs);
        }
    }
    
    /**返回查询结果的第一行第一列*/
    public static Object execScalar(Connection p_conn, String p_sql, Object[] p_paramters) throws SQLException {
        ResultSet rs = null;
        PreparedStatement pre = null;
        
        int index = 1;
        try {
            pre = p_conn.prepareStatement(p_sql);
            if(p_paramters!=null){
	            for (int i = 0; i < p_paramters.length; i++) {
	                if (p_paramters[i] == null) {
	                    pre.setObject(index++, "");
	                } else {
	                    pre.setObject(index++, p_paramters[i]);
	                }
	            }
            }
            
            rs = pre.executeQuery();
            Object retObj = null;
            if(rs.next()){
            	retObj = rs.getObject(1);
            }
            return retObj;
        } catch (SQLException e) {
            throw new SQLException(e);
        } finally {
            close(pre, rs);
        }
    }

    public static List<Map<String, Object>> execQuery(Connection p_conn, String p_sql, Object[] p_paramters) throws SQLException {
        ResultSet rs = null;
        PreparedStatement pre = null;
        List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
        int index = 1;
        try {
            pre = p_conn.prepareStatement(p_sql);
            if(p_paramters!=null){
            	for (int i = 0; i < p_paramters.length; i++) {
                    if (p_paramters[i] == null) {
                        pre.setObject(index++, "");
                    } else {
                        pre.setObject(index++, p_paramters[i]);
                    }
                }
            }
            rs = pre.executeQuery();
            //System.out.println(pre.toString());
            ResultSetMetaData metaData = rs.getMetaData();
            int cols_len = metaData.getColumnCount();
            while (rs.next()){
                Map<String, Object> map = new HashMap<String, Object>();
                for (int i = 0; i < cols_len; i++) {
                    String cols_name = metaData.getColumnLabel(i + 1);
                    Object cols_value = rs.getObject(cols_name);
                    if (cols_value == null) {
                        cols_value = "";
                    }
                    map.put(cols_name.toUpperCase(), cols_value);
                }
                list.add(map);
            }
            return list;
        } catch (SQLException e) {
            throw new SQLException(e);
        } finally {
            close(pre, rs);
        }
    }

    /**将结果拼成json串
     * @throws ParseException */
    public static StringBuilder execQueryToJson(Connection p_conn, String p_sql, Object[] p_paramters) throws SQLException, ParseException {
        ResultSet rs = null;
        PreparedStatement pre = null;
        StringBuilder sbJson = new StringBuilder();
        int index = 1;
        sbJson.append("{");
        try {
            pre = p_conn.prepareStatement(p_sql);
            if(p_paramters!=null){
            	for (int i = 0; i < p_paramters.length; i++) {
                    if (p_paramters[i] == null) {
                        pre.setObject(index++, "");
                    } else {
                        pre.setObject(index++, p_paramters[i]);
                    }
                }
            }
            rs = pre.executeQuery();
            //System.out.println(pre.toString());
            sbJson.append("\"respCode\":" + "\"0\",\r\n");
            sbJson.append("\"respInfo\":" + "\"\",\r\n");
            
            ResultSetMetaData metaData = rs.getMetaData();
            int cols_len = metaData.getColumnCount();
            sbJson.append("\"columns\":[");
            for (int i = 0; i < cols_len; i++) {
            	if(i>0){
            		sbJson.append(",");
            	}
            	sbJson.append("\""+metaData.getColumnLabel(i + 1)+"\"");
            }
            sbJson.append("],\r\n");
            
            sbJson.append("\"dataList\":[");
            int rdCount = 0;
            while (rs.next()){
            	if (rdCount > 0) {
                    sbJson.append(",");
                }
            	rdCount++;
            	sbJson.append("{");
                //Map<String, Object> map = new HashMap<String, Object>();
                for (int i = 0; i < cols_len; i++) {
                	String eachVal = "";
                	String eachType = "";
                    String cols_name = metaData.getColumnLabel(i + 1);
                    Object objVal = rs.getObject(cols_name);
                    if (objVal != null) {
                    	eachVal = objVal.toString();
                    	eachType = objVal.getClass().toString().toLowerCase();
                    }
                    
                    if(eachType.contains("date") && objVal!=null){
						if(eachVal.contains("-") && eachVal.contains(":")){
							java.util.Date eachDate = new SimpleDateFormat(Format.DateIsoFormatStr).parse(eachVal);
							eachVal = Format.DateTimeFormat.format(eachDate);
						}
                    }
                    if(i>0){
                    	sbJson.append(",");
                    }
                    sbJson.append("\"" + cols_name + "\":\"" + eachVal.replace("\\", "\\\\").replace("\"", "\\\"").replace("\n", "\\n").replace("\r", "\\r") + "\"");
                }
                sbJson.append("}\r\n");
            }
            sbJson.append("]");
        } catch (SQLException e) {
        	sbJson.append("\"respCode\":" + "\"-1\",");
            sbJson.append("\"respInfo\":" + "\"" + e.getMessage() + "\"");
            FGBiz.limitMsg.exception("execQueryToJson", e);
            throw new SQLException(e);
        } finally {
            close(pre, rs);
        }
        sbJson.append("}");
        return sbJson;
    }
    
    public static int update(Connection p_conn, String p_sql, Object[] p_paramters) throws SQLException {
        PreparedStatement pre = null;
        int index = 1;
        try{
            pre = p_conn.prepareStatement(p_sql);
            if(p_paramters!=null){
	            for (int i = 0; i < p_paramters.length; i++) {
	            	Object tmpParam = p_paramters[i];
	            	if (tmpParam instanceof Date) {
	            		pre.setObject(index++,  new Timestamp(((Date) tmpParam).getTime()));
	            	} else if (tmpParam instanceof Timestamp) {
	            		pre.setObject(index++,  tmpParam);
	            	} else {
	            		pre.setObject(index++, tmpParam);
	            	}
	            }
            }
            return pre.executeUpdate();

        } catch (SQLException e) {
            throw(e);
        } finally {
            close(pre);
        }
    }
}
